
--set hive.mapred.mode ='proceed'
insert overwrite table bidefault.dm_alarm_cainiao_actual_network_tn_dt
select
    date_time --查询日期
     ,count_t1 --当天时效数量
     ,count_t2 --前1天时效数量
     ,up_diff_count --差异1：前1天有后1天无
     ,current_diff_count --差异2：前1天没有后1天有
     ,aging_diff_cnt --差异3：时效不同
     ,(up_diff_count+current_diff_count+aging_diff_cnt) as diff_count --总差异量
     ,cast((up_diff_count+current_diff_count+aging_diff_cnt)/count_t1 as decimal(16,4)) as diff_rt --差异占比
     ,date_time as dt  --分区
from
    (
        select '{{ execution_date  | cst_ds }}' as date_time,count(t1.days) as  count_t1, count(t2.days) count_t2,
               count(case when t2.days is not null and t1.days is null then 1 end) as up_diff_count,
               count(case when t2.days is null and t1.days is not null then 1 end) as current_diff_count,
--count(case when t2.days is not null and t1.days is not null and t1.days<>t2.days then 1 end) as aging_diff_cnt,
               count(case when t2.route_id is not null and t1.route_id is not null and t1.route_id<>t2.route_id then 1 end) as aging_diff_cnt
        from (
                 select
                     start_hm,
                     end_hm,
                     taking_code,
                     deliver_code,
                     days,
                     route_id
                 from bidefault.dm_cainiao_actual_network_tn_dt
                 where dt='{{ execution_date  | cst_ds }}'
             ) t1
                 full join (
            select
                start_hm,
                end_hm,
                taking_code,
                deliver_code,
                days,
                route_id
            from bidefault.dm_cainiao_actual_network_tn_dt
            where dt=date_add('{{ execution_date  | cst_ds }}',-1)
        ) t2 on t1.start_hm=t2.start_hm  and t1.end_hm=t2.end_hm  and t1.taking_code=t2.taking_code and t1.deliver_code=t2.deliver_code
    )tt
    distribute by 1;
